package myta.bean;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.faces.component.html.HtmlDataTable;
import javax.faces.component.html.HtmlInputText;
import javax.faces.component.html.HtmlSelectOneMenu;
import javax.faces.model.SelectItem;

import myta.base.FormDataBase;
import myta.model.DataRow;
import myta.model.DataTable;
import myta.service.Command;
import myta.service.SQLexec;
import myta.service.Utility;

public class KelasSiswaData extends FormDataBase{
	private HtmlInputText txtid;
	private HtmlSelectOneMenu txtidkelas;
	private HtmlSelectOneMenu txtnis;
	private HtmlDataTable htmldatatable;
	
	private List<SelectItem> listKelas;
	private List<SelectItem> listNis;
	
	@Override
	public void init() throws Exception {
		formName = "kelassiswa";
		
		txtid = new HtmlInputText();
		txtidkelas = new HtmlSelectOneMenu();
		txtnis = new HtmlSelectOneMenu();
		
		loadList();
	}
	
	private void loadList() throws Exception{
		listKelas = new ArrayList<SelectItem>();
		listNis = new ArrayList<SelectItem>();
		
		String sql = "select k.idkelas, concat(k.namakelas,' ~ ',s.thnajaran) as namakelas from kelas k inner join semester s on k.idsemester = s.idsemester order by k.idkelas";
		Command command = SQLexec.makeCommand(sql);
		DataTable dt = command.executeQuery();
		
		for(int i=0;i<dt.getRowCount();i++){
			DataRow dr = dt.getRow(i);
			
			SelectItem s = new SelectItem();
			s.setValue(dr.getString("idkelas"));
			s.setLabel(dr.getString("namakelas"));
			
			listKelas.add(s);
		}
		
		sql = "select * from siswa order by nis";
		command = SQLexec.makeCommand(sql);
		dt = command.executeQuery();
		
		for(int i=0;i<dt.getRowCount();i++){
			DataRow dr = dt.getRow(i);
			
			SelectItem s = new SelectItem();
			s.setValue(dr.getString("nis"));
			s.setLabel(dr.getString("nama"));
			
			listNis.add(s);
		}
	}
	
	public void tambahSiswa(){
		try {
			Session s = (Session)Utility.getBean("sessionBean");
			DataTable dt = s.getTableDetailSiswaKelas();
			
			DataRow dr = new DataRow();
			dr.setColumns(dt.getDataColumns());
			dr.add(txtnis.getValue().toString());
			dr.add(getNamaSiswa(txtnis.getValue().toString()));
			
			dt.addRow(dr);
		} catch (Exception e) {
			Utility.showMessage("Tambah Siswa Error : " + e.getMessage());
		}
	}
	
	public void hapusSiswa(){
		try {
			Session s = (Session)Utility.getBean("sessionBean");
			s.getTableDetailSiswaKelas().removeRow(htmldatatable.getRowIndex());
		} catch (Exception e) {
			Utility.showMessage("Hapus Siswa Error : " + e.getMessage());
		}
	}
	
	private String getNamaSiswa(String nis) throws Exception{
		String sql = "select nama from siswa where nis = ?";
		Command command = SQLexec.makeCommand(sql);
		command.setObject(1, nis);
		DataTable dt = command.executeQuery();
		return dt.getRow(0).getString("nama");
	}

	@Override
	public void generateID() throws Exception {
		String sql = "call idkelassiswa()";
		Command command = SQLexec.makeCommand(sql);
		DataTable datatable = command.executeQuery();
		DataRow datarow = datatable.getRow(0);
		txtid.setValue(datarow.getString("id"));
	}

	@Override
	public void getRequestData(Map<String, String> map) throws Exception {
		if(map.containsKey("idkelassiswa")){
			String idkelassiswa = map.get("idkelassiswa");
			
			String sql = "select * from kelassiswa where idkelassiswa = ?";
			
			Command command = SQLexec.makeCommand(sql);
			command.setObject(1, idkelassiswa);
			DataTable datatable = command.executeQuery();
			DataRow datarow = datatable.getRow(0);
			
			txtid.setValue(datarow.getString("idkelassiswa"));
			txtidkelas.setValue(datarow.getString("idkelas"));
			
			
			Session s = (Session)Utility.getBean("sessionBean");
			
			sql = "select ksd.nis, s.nama from kelassiswa_detail ksd inner join siswa s on ksd.nis = s.nis where ksd.idkelassiswa = ?";
			command = SQLexec.makeCommand(sql);
			command.setObject(1, idkelassiswa);
			s.setTableDetailSiswaKelas(command.executeQuery());
		}
	}

	@Override
	public boolean simpan() throws Exception {
		boolean boo = false;
		
		String sql = "INSERT INTO kelassiswa(idkelassiswa,idkelas) VALUES(?,?);";
		Command command = SQLexec.makeCommand(sql);
		command.setObject(1, txtid.getValue().toString());
		command.setObject(2, txtidkelas.getValue().toString());
		boo = command.executeUpdate();
		
		Session s = (Session)Utility.getBean("sessionBean");
		DataTable dt = s.getTableDetailSiswaKelas();
		
		for(int i=0;i<dt.getRowCount();i++){
			DataRow dr = dt.getRow(i);
			
			sql = "INSERT INTO kelassiswa_detail(idkelassiswa,nourut,nis) VALUES(?,?,?);";
			command = SQLexec.makeCommand(sql);
			command.setObject(1, txtid.getValue().toString());
			command.setObject(2, i+1);
			command.setObject(3, dr.getString(0));
			boo = command.executeUpdate();
		}
		
		boo = beforeCancel();
		
		if(boo){
			return true;
		}else{
			return false;
		}		
	}

	@Override
	public boolean ubah() throws Exception {
		boolean boo = false;
		
		String sql = "delete from kelassiswa where idkelassiswa = ?;";
		Command command = SQLexec.makeCommand(sql);
		command.setObject(1, txtid.getValue().toString());
		boo = command.executeUpdate();
		
		boo = simpan();
		
		if(boo){
			return true;
		}else{
			return false;
		}
	}
	
	@Override
	public boolean beforeCancel() {
		Session s = (Session)Utility.getBean("sessionBean");
		DataTable dt = s.getTableDetailSiswaKelas();
		
		for(int i=dt.getRowCount();i>0;i--){
			dt.removeRow(i-1);
		}
		
		return true;
	}

	public HtmlInputText getTxtid() {
		return txtid;
	}

	public void setTxtid(HtmlInputText txtid) {
		this.txtid = txtid;
	}

	public HtmlSelectOneMenu getTxtidkelas() {
		return txtidkelas;
	}

	public void setTxtidkelas(HtmlSelectOneMenu txtidkelas) {
		this.txtidkelas = txtidkelas;
	}

	public HtmlSelectOneMenu getTxtnis() {
		return txtnis;
	}

	public void setTxtnis(HtmlSelectOneMenu txtnis) {
		this.txtnis = txtnis;
	}

	public List<SelectItem> getListKelas() {
		return listKelas;
	}

	public void setListKelas(List<SelectItem> listKelas) {
		this.listKelas = listKelas;
	}

	public List<SelectItem> getListNis() {
		return listNis;
	}

	public void setListNis(List<SelectItem> listNis) {
		this.listNis = listNis;
	}

	public HtmlDataTable getHtmldatatable() {
		return htmldatatable;
	}

	public void setHtmldatatable(HtmlDataTable htmldatatable) {
		this.htmldatatable = htmldatatable;
	}
}
